/* eslint-disable @typescript-eslint/semi */
/* eslint-disable class-methods-use-this */
/* eslint-disable prefer-template */

import { SqlHelper } from '../../lib/utils/sqlHelper'
import { DbColumn } from '../../models/dbColumn'
import { DbForeignKey } from '../../models/dbForeignKey'
import { DbIndex } from '../../models/dbIndex'
import { DbModel } from '../../models/dbModel'
import { DbTable } from '../../models/dbTable'
import { DbProvider, line } from '../core/dbProvider'

export class MsSqlProvider extends DbProvider {
	private outputSecondChannel = 1

	private SQL_Statement_End = ';'

	private foreignKeyNameVariable = '@FkName'

	public getCreateTableIfNotExistsSql(model: DbModel) {
		this.outputInitialize()

		model.tables.forEach((table) => {
			const { sqlCreateTable, sqlCreateFK } = this.getSqlCreateTableIfNotExistsSingle(table)
			this.outputlnln(sqlCreateTable + line + this.SQL_Statement_End)
			if (sqlCreateFK.length > 0) {
				this.outputlnln(sqlCreateFK + this.SQL_Statement_End, this.outputSecondChannel)
			}
		})

		return this.outputCopy()
	}

	public getCreateTableSql(model: DbModel, withComment: boolean) {
		this.outputInitialize()
		this.outputCreateTableSQL(model, withComment)
		return this.outputCopy()
	}

	public getDropTableSql(model: DbModel) {
		this.outputInitialize()
		this.OutputDropTableSQL(model)
		return this.outputCopy()
	}

	public getDropThenCreateTableSql(model: DbModel, withComment: boolean) {
		this.outputInitialize()
		this.OutputDropTableSQL(model)
		this.outputCreateTableSQL(model, withComment)
		return this.outputCopy()
	}

	private getSqlCreateTableIfNotExistsSingle(table: DbTable): {
		sqlCreateFK: string
		sqlCreateTable: string
	} {
		const result = this.getSqlCreateTableSingle(table, false)
		const sSQL =
			'IF NOT EXISTS (' +
			line +
			'  SELECT *' +
			line +
			'    FROM INFORMATION_SCHEMA.TABLES' +
			line +
			"   WHERE TABLE_TYPE = 'BASE TABLE'" +
			line +
			"     AND TABLE_SCHEMA = 'dbo'" +
			line +
			`     AND TABLE_NAME = '${table.name}'` +
			line +
			'  )' +
			line +
			'BEGIN' +
			line +
			result.sqlCreateTable +
			line +
			'END'

		result.sqlCreateTable = sSQL
		return result
	}

	public getSqlCreateTableSingle(
		table: DbTable,
		withComment: boolean,
	): {
		sqlCreateFK: string
		sqlCreateTable: string
	} {
		const result = {
			sqlCreateFK: '',
			sqlCreateTable: '',
		}

		if (!table.columns) {
			return result
		}

		// Create Columns sql
		let sqlColumn = ''
		table.columns.forEach((column: DbColumn, index) => {
			const sqlComma = index === 0 ? ' ' : ','
			const sqlNullable = column.nullable ? 'NULL' : 'NOT NULL'
			const sqlDefault =
				column.default!.length === 0
					? ''
					: ` CONSTRAINT ${this.renderDefaultName(table, column)} DEFAULT (${this.stripBrackets(
							column.default!,
							true,
					  )})`
			sqlColumn += line + `  ${sqlComma}[${column.name}] ${column.type} ${sqlNullable}${sqlDefault}`
		})

		// Primary key SQL
		let sqlPrimaryKey = ''
		if (table.primaryKey) {
			const sqlPkName = this.renderPkName(table)
			const sqlCluster = table.primaryKey.cluster ? 'CLUSTERED' : 'NONCLUSTERED'
			const sqlPkColumns = table.primaryKey.columns.replace(',\\s*', ', ')
			sqlPrimaryKey =
				line + `  ,CONSTRAINT ${sqlPkName} PRIMARY KEY ${sqlCluster} (${sqlPkColumns})`
		}

		// Unique Indexes SQL
		let sqlUniqueIndex = ''
		table.indexes?.forEach((index: DbIndex) => {
			if (index.unique) {
				const sqlIndexName = this.renderIndexName(table, index)
				const sqlColumns = index.columns.replace(',\\s*', ', ')
				const sqlCluster = index.cluster ? ' CLUSTERED' : ''
				sqlUniqueIndex += line + `  ,CONSTRAINT ${sqlIndexName} UNIQUE${sqlCluster} (${sqlColumns})`
			}
		})

		// non-unique Indexes SQL
		let sqlNoUniqueIndex = ''
		table.indexes?.forEach((index: DbIndex) => {
			if (!index.unique) {
				const sqlIndexName = this.renderIndexName(table, index)
				const sqlColumns = index.columns.replace(',\\s*', ', ')
				const sqlCluster = index.cluster ? ' CLUSTERED' : ''
				sqlNoUniqueIndex +=
					line + `CREATE${sqlCluster} INDEX ${sqlIndexName} ON [${table.name}] (${sqlColumns})`
			}
		})

		// Generate table sql
		result.sqlCreateTable =
			`-- Create table '${table.name}'` +
			line +
			`CREATE TABLE [${table.name}] (` +
			sqlColumn +
			sqlPrimaryKey +
			sqlUniqueIndex +
			line +
			')' +
			sqlNoUniqueIndex

		// Generate Foreign key SQL
		if (table.foreignKeys.length > 0) {
			result.sqlCreateFK =
				`-- Foreign keys for table '${table.name}'` + line + `ALTER TABLE [${table.name}] ADD`

			table.foreignKeys?.forEach((foreignKey: DbForeignKey, index) => {
				const sqlFirst = index === 0 ? '' : ','
				const sqlFkName = this.renderFkName(table, foreignKey)
				const sqlColumns = foreignKey.columns.replace(',\\s*', ', ')
				const sqlRefInfo = `${foreignKey.refTable}(${foreignKey.refColumns.replace(',\\s*', ', ')})`
				const sqlOptions = foreignKey.options!.length > 0 ? foreignKey.options : ''

				result.sqlCreateFK +=
					line +
					`${sqlFirst} CONSTRAINT ${sqlFkName}` +
					line +
					`  FOREIGN KEY (${sqlColumns})` +
					line +
					`  REFERENCES ${sqlRefInfo}${sqlOptions}`
			})
		}

		// Column comment
		let sqlComment = ''
		if (withComment) {
			// For table comment
			if (table.comment) {
				sqlComment +=
					line +
					"EXECUTE sp_addextendedproperty N'MS_Description'" +
					line +
					`  , N${SqlHelper.toSQL(table.comment)}` +
					line +
					`  , N'user', N'dbo', N'table', N'${table.name}'`
			}

			// For column comment
			table.columns.forEach((column: DbColumn) => {
				sqlComment +=
					line +
					"EXECUTE sp_addextendedproperty N'MS_Description'" +
					line +
					`  , N${SqlHelper.toSQL(this.getColumnComment(column))}` +
					line +
					`  , N'user', N'dbo', N'table', N'${table.name}', N'column', N'${column.name}'`
			})
			if (sqlComment) {
				result.sqlCreateTable += line + sqlComment
			}
		}
		return result
	}

	private getSqlDropTableSingle(table: DbTable): string {
		const sSQL =
			`-- Drop table '${table.name}'` +
			line +
			'IF EXISTS (' +
			line +
			'  SELECT * FROM INFORMATION_SCHEMA.TABLES' +
			line +
			"  WHERE TABLE_TYPE = 'BASE TABLE'" +
			line +
			"  AND TABLE_SCHEMA = 'dbo'" +
			line +
			`  AND TABLE_NAME = '${table.name}'` +
			line +
			'  )' +
			line +
			'BEGIN' +
			line +
			`  DROP TABLE [dbo].[${table.name}]` +
			line +
			'END'

		return sSQL
	}

	private getSqlRemoveFkSingle(table: DbTable): string {
		const sSQL =
			`-- Drop foreign keys for table '${table.name}'` +
			line +
			'DECLARE fk_cursor CURSOR FOR ' +
			line +
			'  SELECT CONSTRAINT_NAME' +
			line +
			'    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS' +
			line +
			"   WHERE TABLE_SCHEMA = 'dbo'" +
			line +
			`     AND TABLE_NAME = '${table.name}'` +
			line +
			"     AND CONSTRAINT_TYPE = 'FOREIGN KEY'" +
			line +
			'ORDER BY CONSTRAINT_NAME' +
			line +
			'' +
			line +
			'OPEN fk_cursor' +
			line +
			`FETCH NEXT FROM fk_cursor INTO ${this.foreignKeyNameVariable}` +
			line +
			'WHILE @@FETCH_STATUS = 0' +
			line +
			'BEGIN' +
			line +
			`  EXEC('ALTER TABLE [${table.name}] DROP CONSTRAINT ' + ${this.foreignKeyNameVariable})` +
			line +
			`  FETCH NEXT FROM fk_cursor INTO ${this.foreignKeyNameVariable}` +
			line +
			'END' +
			line +
			'' +
			line +
			'CLOSE fk_cursor' +
			line +
			'DEALLOCATE fk_cursor'

		// Return
		return sSQL
	}

	public outputCreateTableSQL(model: DbModel, withComment: boolean) {
		// Create tables
		model.tables.forEach((table) => {
			const { sqlCreateTable, sqlCreateFK } = this.getSqlCreateTableSingle(table, withComment)
			this.outputlnln(sqlCreateTable + line + this.SQL_Statement_End)
			if (sqlCreateFK.length > 0) {
				this.outputlnln(sqlCreateFK + line + this.SQL_Statement_End, this.outputSecondChannel)
			}
		})
	}

	private OutputDropTableSQL(model: DbModel) {
		let sSQL: string

		// Prepare to drop foreign keys
		sSQL =
			'-- Prepare to remove foreign keys' + line + `DECLARE ${this.foreignKeyNameVariable}  SYSNAME`
		this.outputlnln(sSQL)

		// Drop foreign key relation
		model.tables.forEach((table) => {
			sSQL = this.getSqlRemoveFkSingle(table)
			this.outputlnln(sSQL + line + this.SQL_Statement_End)
		})

		// Drop tables
		model.tables.forEach((table) => {
			sSQL = this.getSqlDropTableSingle(table)
			this.outputlnln(sSQL + line + this.SQL_Statement_End)
		})
	}
}
